﻿CREATE PROCEDURE [dbo].[proc_L_R_FinanceApproved_GetList]
	(
		@CompanyId VARCHAR(500),	
		@DepartmentId INT,
		@EName NVARCHAR(10),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@flag INT,
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	
	DECLARE @str VARCHAR(1000),@Sqlstr VARCHAR(2000)
	
	SET @str=''
	
	IF @CompanyId<>'' and @CompanyId<>'0'
	BEGIN
		SET @str=' CompanyId in ('+@CompanyId+') and '
	END
	IF @DepartmentId<>0
	BEGIN
		SET @str=@str+' DepartmentId in (SELECT id FROM (
 SELECT ( SELECT	d.bmbh FROM	Department d WHERE id='+CONVERT(VARCHAR,@DepartmentId)+') AS a) AS nb1,Department 
WHERE LEFT(bmbh,LEN(a))=a) and '
	END
	IF @EName<>''
	BEGIN
		SET @str=@str+' EName like ''%'+@EName+'%'' and '
	END
	IF @StartDate<>'' and @EndDate<>''
	BEGIN
		SET @str=@str+' Convert(VARCHAR(10),lrrq,120) between '''+@StartDate+''' and '''+@EndDate+''' and '
	END
	IF(@flag<>-2)
	BEGIN
		SET @str=@str+' flag ='+CONVERT(VARCHAR, @flag)+' and '
	END
	ELSE
		BEGIN
			SET @str=@str+' flag in (1,0) and '
		END
	
	IF @str<>''
	BEGIN
		set @str=' where PZflag1=1 and '+SUBSTRING(@str,1,LEN(@str)-3)
	END
	
	SET @Sqlstr=' ;WITH list As( SELECT 
	  ROW_NUMBER() OVER (ORDER BY flag ,lrrq desc)AS Row
	  ,id,[Summary]
      ,[CompanyId]
      ,[CompanyName]
      ,[DepartmentId]
      ,[DepartmentName]
      ,[ENameId]
      ,[EName]
      ,[PZflag]
      ,[PZPerson]
      ,[PZPersonName]
      ,[PZYJ]
      ,[PZRQ]
      ,[PZflag1]
      ,[PZPerson1]
      ,[PZPersonName1]
      ,[PZYJ1]
      ,[PZRQ1]
      ,[SHflag]
      ,[SHPerson]
      ,[SHPersonName]
      ,[SHYJ]
      ,[SHRQ]
      ,[Memo]
      ,[lrrq]
      ,[userid]
      ,[username]
      ,[Money]
      ,flag
       FROM L_Reimbursement '+@str +')
      
      select *,(select count(1) from list) as cid,(select sum(Money) from list) as zje 
      from list where Row Between '+CONVERT(VARCHAR,@StartIndex)+' and '+CONVERT(VARCHAR,@EndIndex)+' 
      '
      	
	PRINT(@Sqlstr)
	EXEC(@Sqlstr)	
end			
	RETURN
